package com.gaotu.demo.jdbc;

import com.gaotu.demo.User;
import com.gaotu.demo.UserDao;
import com.gaotu.demo.UserStatus;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Slf4j
@Component
public class PreparedStatementBasedUserDao implements UserDao {
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String userName;
    @Value("${spring.datasource.password}")
    private String password;

    @Autowired
    private DataSource dataSource;


    @Override
    public void insert(User user) {


        try (Connection connection = getConnectionFromDataSource()){
            String sql = "insert into tb_user(`create_time`,`update_time`,`status`,`name`, `password`)" +
                    " values (?, ?, ?, ?, ?)";
            try (PreparedStatement preparedStatement =
                         connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)){
                preparedStatement.setDate(1, toSqlDate(user.getCreateTime()));
                preparedStatement.setDate(2, toSqlDate(user.getUpdateTime()));
                preparedStatement.setInt(3, user.getStatus().getCode());
                preparedStatement.setString(4, user.getName());
                preparedStatement.setString(5, user.getPassword());
                int row = preparedStatement.executeUpdate();
                if (row > 0){
                    try(ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                        if (generatedKeys.next()){
                            long id = generatedKeys.getLong(1);
                            user.setId(id);
                        }
                    }
                }
            }catch (Exception e){
                log.error("failed to  insert user {}", user, e);
            }
        } catch (SQLException e) {
            log.error("failed to  insert user {}", user, e);
        }
    }



    @Override
    public void update(User user) {
        try (Connection connection = getConnectionFromDataSource()){
            String sql ="update tb_user set update_time = ?, status = ?, name = ?, password = ? " +
                    "where id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                preparedStatement.setDate(1, toSqlDate(user.getUpdateTime()));
                preparedStatement.setInt(2, user.getStatus().getCode());
                preparedStatement.setString(3, user.getName());
                preparedStatement.setString(4, user.getPassword());
                preparedStatement.setLong(5, user.getId());
                preparedStatement.executeUpdate();
            }catch (Exception e){
                log.error("failed to update user {}", user, e);
            }
        } catch (SQLException e) {
            log.error("failed to  update user {}", user, e);
        }
    }

    @Override
    public void deleteById(Long id) {

        try (Connection connection = getConnectionFromDataSource()){
            String sql = "delete from tb_user where id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                preparedStatement.setLong(1, id);
                preparedStatement.executeUpdate();
            }catch (Exception e){
                log.error("failed to delete user by id {}", id, e);
            }
        } catch (SQLException e) {
            log.error("failed to delete user by id {}", id, e);
        }
    }

    @Override
    public User getById(Long id) {
        try (Connection connection = getConnectionFromDataSource()){
            String sql ="select id, create_time, update_time, status, name, password " +
                    "from tb_user  where id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                preparedStatement.setLong(1, id);
                try(ResultSet resultSet = preparedStatement.executeQuery()) {
                    if (resultSet.next()){
                        Long idForSelect = resultSet.getLong(1);
                        java.sql.Date createDate = resultSet.getDate(2);
                        java.sql.Date updateDate = resultSet.getDate(3);
                        Integer statusCode = resultSet.getInt(4);
                        String name = resultSet.getString(5);
                        String password = resultSet.getString(6);
                        User user = new User();
                        user.setId(idForSelect);
                        user.setCreateTime(createDate);
                        user.setUpdateTime(updateDate);
                        user.setStatus(UserStatus.valueOfCode(statusCode));
                        user.setName(name);
                        user.setPassword(password);
                        return user;
                    }
                }
            }catch (Exception e){
                log.error("failed to select user by {}", id, e);
            }
        } catch (SQLException e) {
            log.error("failed to select user by {}", id, e);
        }
        return null;
    }

    @Override
    public User getByName(String name) {
        String sql = "select " +
                        "id, create_time, update_time, status, name, password " +
                    "from tb_user " +
                    "where " +
                        "name = ?";
        try (Connection connection = getConnectionFromDataSource()){
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                preparedStatement.setString(1, name);

                try (ResultSet resultSet = preparedStatement.executeQuery()){
                    if (resultSet.next()){
                        Long id = resultSet.getLong(1);
                        java.sql.Date createDate = resultSet.getDate(2);
                        java.sql.Date updateDate = resultSet.getDate(3);
                        Integer statusCode = resultSet.getInt(4);
                        String nameForSelect = resultSet.getString(5);
                        String password = resultSet.getString(6);
                        User user = new User();
                        user.setId(id);
                        user.setCreateTime(createDate);
                        user.setUpdateTime(updateDate);
                        user.setStatus(UserStatus.valueOfCode(statusCode));
                        user.setName(nameForSelect);
                        user.setPassword(password);
                        return user;
                    }
                }
            }catch (Exception e){
                log.error("failed to select user by name {}", name, e);
            }
        } catch (SQLException e) {
            log.error("failed to select user by name {}", name, e);
        }
        return null;
    }

    private Connection getConnectionFromDataSource() throws SQLException {
        return this.dataSource.getConnection();
    }

    private java.sql.Date toSqlDate(Date date) {
        return new java.sql.Date(date.getTime());
    }

    private Connection getConnectionFromDriverManger() throws SQLException {
        try {
            Class.forName(this.driverClassName);
        } catch (ClassNotFoundException e) {
            log.error("failed to find driver {}", this.driverClassName, e);
        }
        return DriverManager.getConnection(url, userName, password);
    }

    private String formatDate(Date date){
        SimpleDateFormat dataFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return dataFormat.format(date);
    }

    private void batchTest(List<Long> ids){
        try (Connection connection = getConnectionFromDataSource()){
            String sql = "delete from tb_user where id = ?";
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)){
                for(Long id : ids) {
                    preparedStatement.setLong(1, id);
                    preparedStatement.addBatch();
                }
                int[] rows = preparedStatement.executeBatch();
            }catch (Exception e){
                log.error("failed to delete user by id {}", ids, e);
            }
        } catch (SQLException e) {
            log.error("failed to delete user by id {}", ids, e);
        }
    }

    private List<User> listUser(){
        List<User> users = new ArrayList<>();
        try(Connection connection = getConnectionFromDataSource()){
            String sql = "select " +
                            "id, create_time, update_time, name, password  " +
                        "from tb_user";
            try (PreparedStatement preparedStatement =
                         connection.prepareStatement(sql)) {
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    Long id = resultSet.getLong(1);
                    java.sql.Date createDate = resultSet.getDate(2);
                    java.sql.Date updateDate = resultSet.getDate(3);
                    String nameForSelect = resultSet.getString(4);
                    String password = resultSet.getString(5);
                    User user = new User();
                    user.setId(id);
                    user.setCreateTime(createDate);
                    user.setUpdateTime(updateDate);
                    user.setName(nameForSelect);
                    user.setPassword(password);
                    users.add(user);
                }
                return users;
            }
        } catch (SQLException e) {
            log.error("failed to execute", e);
        }
        return null;
    }

}
